package com.example.zhinongbao.provider;

import static com.example.zhinongbao.provider.ZhinongbaoContract.FriendCircle.COLUMN_IS_LIKED;
import static com.example.zhinongbao.provider.ZhinongbaoContract.FriendCircle.TABLE_NAME;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class ZhinongbaoDbHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "zhinongbao.db";
    private static final int DATABASE_VERSION = 9; // 升级版本号

    public ZhinongbaoDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createUsersTable(db);
        createProductsTable(db);
        createFootprintsTable(db);
        createMessagesTable(db);
        createOrdersTable(db);
        createNewsTable(db);
        createFriendCircleTable(db);
        createUserFollowTable(db);
    }

    private void createUsersTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + ZhinongbaoContract.Users.TABLE_NAME + " ("
                + ZhinongbaoContract.Users._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + ZhinongbaoContract.Users.COLUMN_USERNAME + " TEXT NOT NULL, "
                + ZhinongbaoContract.Users.COLUMN_PASSWORD + " TEXT NOT NULL, "
                + ZhinongbaoContract.Users.COLUMN_AVATAR + " TEXT, "
                + ZhinongbaoContract.Users.COLUMN_PHONE + " TEXT, "
                + ZhinongbaoContract.Users.COLUMN_EMAIL + " TEXT, "
                + ZhinongbaoContract.Users.COLUMN_TYPE + " INTEGER DEFAULT 0, "
                + ZhinongbaoContract.Users.COLUMN_CREATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
                + ")");
    }

    private void createUserFollowTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + ZhinongbaoContract.UserFollow.TABLE_NAME + " ("
                + ZhinongbaoContract.UserFollow._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + ZhinongbaoContract.UserFollow.COLUMN_USER_ID + " INTEGER NOT NULL, "
                + ZhinongbaoContract.UserFollow.COLUMN_FOLLOW_ID + " INTEGER NOT NULL, "
                + ZhinongbaoContract.UserFollow.COLUMN_CREATE_TIME + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
                + "UNIQUE(" + ZhinongbaoContract.UserFollow.COLUMN_USER_ID + ", "
                + ZhinongbaoContract.UserFollow.COLUMN_FOLLOW_ID + ")"
                + ")");
    }

    private void createFriendCircleTable(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + TABLE_NAME + " (" +
                ZhinongbaoContract.FriendCircle._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                ZhinongbaoContract.FriendCircle.COLUMN_CONTENT + " TEXT NOT NULL, " +
                ZhinongbaoContract.FriendCircle.COLUMN_IMAGES + " TEXT, " +
                ZhinongbaoContract.FriendCircle.COLUMN_USER_ID + " INTEGER NOT NULL, " +
                ZhinongbaoContract.FriendCircle.COLUMN_USER_NAME + " TEXT NOT NULL, " +
                ZhinongbaoContract.FriendCircle.COLUMN_USER_AVATAR + " TEXT, " +
                ZhinongbaoContract.FriendCircle.COLUMN_CREATE_TIME + " INTEGER NOT NULL, " +
                ZhinongbaoContract.FriendCircle.COLUMN_TYPE + " INTEGER DEFAULT 0, " + // 添加type列
                ZhinongbaoContract.FriendCircle.COLUMN_IS_FOLLOWED + " INTEGER DEFAULT 0, " +
                ZhinongbaoContract.FriendCircle.COLUMN_IS_LIKED + " INTEGER DEFAULT 0, " +
                ZhinongbaoContract.FriendCircle.COLUMN_LIKE_COUNT + " INTEGER DEFAULT 0" +
                ")";
        db.execSQL(sql);
    }

    private void createProductsTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + ZhinongbaoContract.Products.TABLE_NAME + " ("
                + ZhinongbaoContract.Products._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + ZhinongbaoContract.Products.COLUMN_NAME + " TEXT NOT NULL, "
                + ZhinongbaoContract.Products.COLUMN_PRICE + " REAL, "
                + ZhinongbaoContract.Products.COLUMN_DESCRIPTION + " TEXT, "
                + ZhinongbaoContract.Products.COLUMN_CREATED_AT + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
                + ")");
    }

    private void createNewsTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + ZhinongbaoContract.NewsEntry.TABLE_NAME + " ("
                + ZhinongbaoContract.NewsEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + ZhinongbaoContract.NewsEntry.COLUMN_TITLE + " TEXT NOT NULL, "
                + ZhinongbaoContract.NewsEntry.COLUMN_CONTENT + " TEXT NOT NULL, "
                + ZhinongbaoContract.NewsEntry.COLUMN_IMAGE_URL + " TEXT, "
                + ZhinongbaoContract.NewsEntry.COLUMN_CREATED_AT + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
                + ZhinongbaoContract.NewsEntry.COLUMN_USER_ID + " INTEGER NOT NULL, "
                + ZhinongbaoContract.NewsEntry.COLUMN_IS_PUBLISHED + " INTEGER DEFAULT 1"
                + ")");
    }

    private void createFootprintsTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + ZhinongbaoContract.FootprintEntry.TABLE_NAME + " ("
                + BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + ZhinongbaoContract.FootprintEntry.COLUMN_PRODUCT_ID + " INTEGER NOT NULL, "
                + ZhinongbaoContract.FootprintEntry.COLUMN_USER_ID + " INTEGER NOT NULL, " // 新增用户ID字段
                + ZhinongbaoContract.FootprintEntry.COLUMN_TIMESTAMP + " INTEGER NOT NULL, "
                + "FOREIGN KEY(" + ZhinongbaoContract.FootprintEntry.COLUMN_PRODUCT_ID + ") "
                + "REFERENCES " + ZhinongbaoContract.Products.TABLE_NAME + "(" + ZhinongbaoContract.Products._ID + ")"
                + ")");
    }

    private void createMessagesTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + ZhinongbaoContract.Messages.TABLE_NAME + " ("
                + ZhinongbaoContract.Messages._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + ZhinongbaoContract.Messages.COLUMN_CONTENT + " TEXT NOT NULL, "
                + ZhinongbaoContract.Messages.COLUMN_TIMESTAMP + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
                + ZhinongbaoContract.Messages.COLUMN_IS_SENT + " INTEGER NOT NULL DEFAULT 0, "
                + ZhinongbaoContract.Messages.COLUMN_USER_ID + " INTEGER NOT NULL" // 新增用户ID字段
                + ")");
    }

    private void createOrdersTable(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + ZhinongbaoContract.Orders.TABLE_NAME + " ("
                + ZhinongbaoContract.Orders._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + ZhinongbaoContract.Orders.COLUMN_PRODUCT_ID + " INTEGER NOT NULL, "
                + ZhinongbaoContract.Orders.COLUMN_USER_ID + " INTEGER NOT NULL, "
                + ZhinongbaoContract.Orders.COLUMN_QUANTITY + " INTEGER NOT NULL, "
                + ZhinongbaoContract.Orders.COLUMN_PRICE + " REAL NOT NULL, "
                + ZhinongbaoContract.Orders.COLUMN_STATUS + " TEXT NOT NULL, "
                + ZhinongbaoContract.Orders.COLUMN_CREATED_AT + " TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "
                + "FOREIGN KEY(" + ZhinongbaoContract.Orders.COLUMN_PRODUCT_ID + ") "
                + "REFERENCES " + ZhinongbaoContract.Products.TABLE_NAME + "(" + ZhinongbaoContract.Products._ID + ")"
                + ")");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d("DB_UPGRADE", "从版本 " + oldVersion + " 升级到 " + newVersion);

        // 增量升级逻辑
        if (oldVersion < 2) {
            createMessagesTable(db);
        }
        if (oldVersion < 3) {
            createFootprintsTable(db);
        }
        if (oldVersion < 4) {
            // 版本4新增字段和订单表
            db.execSQL("ALTER TABLE " + ZhinongbaoContract.FootprintEntry.TABLE_NAME
                    + " ADD COLUMN " + ZhinongbaoContract.FootprintEntry.COLUMN_USER_ID + " INTEGER DEFAULT 0");

            db.execSQL("ALTER TABLE " + ZhinongbaoContract.Messages.TABLE_NAME
                    + " ADD COLUMN " + ZhinongbaoContract.Messages.COLUMN_USER_ID + " INTEGER DEFAULT 0");

            createOrdersTable(db);
        }
        if (oldVersion < 5) {
            createNewsTable(db);
            Log.d("DB_UPGRADE", "创建新闻表");
        }
        if (oldVersion < 6) {
            createUsersTable(db);
            Log.d("DB_UPGRADE", "创建用户表");
            createFriendCircleTable(db);
            createUserFollowTable(db);
            Log.d("DB_UPGRADE", "创建农友圈和用户关注表");
        }
        if (oldVersion < 7) {
            // 版本7新增农友圈关注状态字段
            db.execSQL("ALTER TABLE " + TABLE_NAME +
                    " ADD COLUMN " + ZhinongbaoContract.FriendCircle.COLUMN_IS_FOLLOWED + " INTEGER DEFAULT 0");
        }
        if (oldVersion < 8) {
            // 添加点赞相关字段
            db.execSQL("ALTER TABLE " + TABLE_NAME +
                    " ADD COLUMN " + ZhinongbaoContract.FriendCircle.COLUMN_LIKE_COUNT + " INTEGER DEFAULT 0");
            db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + COLUMN_IS_LIKED + " INTEGER DEFAULT 0");
        }
        if (oldVersion < 9) {
            // 添加type列到friend_circle表
            db.execSQL("ALTER TABLE " + ZhinongbaoContract.FriendCircle.TABLE_NAME +
                    " ADD COLUMN " + ZhinongbaoContract.FriendCircle.COLUMN_TYPE + " INTEGER DEFAULT 0");
        }
    }
}